﻿
CREATE PROCEDURE [dbo].[proc_Statistics_Performance_Zihai]
	@sRq varchar(10),
	@eRq varchar(10),
	@companyId varchar(1000)
AS
BEGIN

	declare @sql varchar(max)
	set @sql='Declare @sRq Datetime,@eRq Datetime,@Yf varchar(7)
	Set @sRq = Convert(Datetime,''' + @sRq + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @eRq + ' 23:59:59'')
	Set @Yf = CONVERT(varchar(10),@sRq,120) 
	'
	--企业QQ成单排名
	Set @sql+='
	Select  ROW_NUMBER() OVER (ORDER BY TotalQuantity desc,TotalAmount desc) AS Rownum,* from (
		Select CompanyId,CompanyName,'''+'企业QQ成单'+''' CategoryName,Sum(qyQQ) TotalQuantity,Sum(qyQQ_je) TotalAmount
		 from [Statistics_Financial] 
		 where CompanyId in (Select CompanyId from Zzjg Where Pid in (281,282) and CompanyId in('+@companyId+'))
			and [CreateDate] between @sRq and @eRq
		 Group by CompanyId,CompanyName) x 
	ORDER BY TotalQuantity desc;'
	--新增毛利
	Set @sql+='
	Select  ROW_NUMBER() OVER (ORDER BY TotalAmount desc) AS Rownum,* from (
		Select CompanyId,CompanyName,'''+'新增毛利'+''' CategoryName,Sum(0) TotalQuantity,Sum(IAA) TotalAmount
		 from [Statistics_Financial] 
		 where CompanyId in (Select CompanyId from Zzjg Where Pid in (281,282) and CompanyId in('+@companyId+'))
			and [CreateDate] between @sRq and @eRq
		 Group by CompanyId,CompanyName) x 
	ORDER BY TotalAmount desc;'
    --续费毛利
	Set @sql+='
	Select  ROW_NUMBER() OVER (ORDER BY TotalAmount desc) AS Rownum,* from (
		Select CompanyId,CompanyName,'''+'续费毛利'+''' CategoryName,Sum(0) TotalQuantity,Sum(IAB) TotalAmount
		 from [Statistics_Financial] 
		 where CompanyId in (Select CompanyId from Zzjg Where Pid in (281,282) and CompanyId in('+@companyId+'))
			and [CreateDate] between @sRq and @eRq
		 Group by CompanyId,CompanyName) x 
	ORDER BY TotalAmount desc;'
	--实收纯利
	Set @sql+='
	 Select  ROW_NUMBER() OVER (ORDER BY TotalAmount desc) AS Rownum,x.CompanyId,x.CompanyName,x.CategoryName,x.TotalQuantity,x.TotalAmount+isnull(p.amount,0) TotalAmout from (
		Select CompanyId,CompanyName,'''+'实收纯利'+''' CategoryName,Sum(0) TotalQuantity,Sum(sscl) TotalAmount
		 from [Statistics_Financial] 
		 where CompanyId in (Select CompanyId from Zzjg Where Pid in (281,282) and CompanyId in('+@companyId+'))
			and [CreateDate] between @sRq and @eRq
		 Group by CompanyId,CompanyName) x 
		 left join 
		 (Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and RegulateDate between @sRq and @eRq and Approval=1
			Group by CompanyId) p
		 on x.CompanyId=p.CompanyId
	ORDER BY TotalAmount desc;'
	exec(@sql)
END
